/*
  Written by Thao Pham Updated by Lory Al Moakar
  Purpose: Demo JDBC for CS1555 Class
  
  IMPORTANT (otherwise, your code may not compile)	
  Same as using sqlplus, you need to set oracle environment variables by 
  sourcing bash.env or tcsh.env
*/

/*IMPORTANT: must refresh mv_manifest after any change on reservation_detail, reservation, customer tables.
query = "call DBMS_MVIEW.REFRESH('mv_manifest','c')"; 
statement.executeUpdate(query); 
*/

import java.sql.*;  //import the file containing definitions for the parts
                    //needed by java for database connection and manipulation
import java.io.*;
import java.util.*;

public class AdminInterface {
    private static Connection connection; //used to hold the jdbc connection to the DB
    private static Statement statement; //used to create an instance of the connection
    private static ResultSet resultSet; //used to hold the result of your query (if one
    // exists)
    private static String query;  //this will hold the query we are using
    private String username, password;
    
    public AdminInterface() {
	
	/* Making a connection to a DB causes certian exceptions.  In order to handle
	   these, you either put the DB stuff in a try block or have your function
	   throw the Execptions and handle them later.  For this demo I will use the
	   try blocks */

	//Scanner kbd = new Scanner(System.in);
	//System.out.print("Enter Username: ");
	//username = kbd.nextLine(); //This is your username in oracle
	//System.out.println();
	//System.out.print("Enter Password: ");
	//password = kbd.nextLine(); //This is your password in oracle
	username = "cac109";
	password = "3437106";
	try{
	    // Register the oracle driver.  
	    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
	    
	    //This is the location of the database.  This is the database in oracle
	    //provided to the class
	    String url = "jdbc:oracle:thin:@db10.cs.pitt.edu:1521:dbclass"; 
	    
	    //create a connection to DB on db10.cs.pitt.edu
	    connection = DriverManager.getConnection(url, username, password); 
	    
	}
	catch(Exception Ex)  {
	    System.out.println("Error connecting to database.  Machine Error: " +
			       Ex.toString());
	}
	
    }//end AdminInterface class

    /////////////////TASK 1 //////////////////////////
    public void Task1() {
	
	try{
	    statement = connection.createStatement(); //create an instance
	    System.out.println("***CLEAR DATABASE***\nDROPPING TUPLES...");
	    query = "DELETE FROM FLIGHT";
	    statement.executeUpdate(query);
	    query = "DELETE FROM PLANE";
	    statement.executeUpdate(query);
	    query = "DELETE FROM PRICE";
	    statement.executeUpdate(query);
	    query = "DELETE FROM CUSTOMER";
	    statement.executeUpdate(query);
	    query = "DELETE FROM RESERVATION";
	    statement.executeUpdate(query);
	    query = "DELETE FROM RESERVATION_DETAIL";
	    statement.executeUpdate(query);
	    //query = "DELETE FROM OUR_SYS_TIME";
	    //statement.executeUpdate(query);
	    System.out.println("ALL TABLES CLEARED");
	    query = "call DBMS_MVIEW.REFRESH('mv_manifest','c')"; 
	    statement.executeUpdate(query);
	    connection.commit();
	    
	}
	catch(Exception Ex) {
	    System.out.println("Error running the sample queries.  Machine Error: " +
			       Ex.toString());
	}
    }//end of task 1
    

    ///////////////////TASK 2////////////////////////
    public void Task2(){
	try{
	    	statement = connection.createStatement();
	    	String flightNum, planeType, from, to, departTime, arriveTime, weekSched;
	    	Scanner kbd = new Scanner(System.in);
	    	String fname = kbd.nextLine();
				BufferedReader infile = new BufferedReader(new FileReader(fname));
				while(infile.ready()){
					String text = infile.readLine();
					String[] parse = text.split(" ");
					flightNum = parse[0];
					planeType = parse[1];
					from = parse[2];
					to = parse[3];
					departTime = parse[4];
					arriveTime = parse[5];
					weekSched = parse[6];
					System.out.println("FLIGHT_NUMBER: "+flightNum+", PLANE_TYPE: "+planeType+", FROM: "+from+", TO: "+to+", DEPARTURE: "+departTime+", ARRIVAL: "+arriveTime+", WEEKLY SCHEDULE: "+weekSched);
					query = "insert into PRICE values('"+flightNum+"', '"+planeType+"', '"+from+"', '"+to+"', '"+departTime+"', '"+arriveTime+"', '"+weekSched+"')" ;
			
					statement.executeUpdate(query);
					//System.out.println("DEBUG");
		  		}//end while loop
				connection.commit(); 
	}
	catch(Exception Ex)  {
		System.out.println("Machine Error: " +
				   Ex.toString());
	    }
    }//end of task2

    ///////////////////TASK 3////////////////////////
    public void Task3(String option) {
	try {
		statement = connection.createStatement();
	    	String from, to, low, high;
		if (option.equals("L")) {
			//load pricing information from file
			Scanner kbd = new Scanner(System.in);
			System.out.print("ENTER INPUT FILE NAME AND PATH: ");
			String fname = kbd.nextLine();
			BufferedReader infile = new BufferedReader(new FileReader(fname));
			while(infile.ready()){
				String text = infile.readLine();
				String[] parse = text.split(" ");
				from = parse[0];
				to = parse[1];
				high = parse[2];
				low = parse[3];
				System.out.println("from: "+from+", to: "+to+", high: $"+high+", low: $"+low);
			query = "insert into PRICE values('"+from+"', '"+to+"', "+high+", "+low+")" ;
			
			statement.executeUpdate(query);
			//System.out.println("DEBUG");
		  	} 
			connection.commit();
		  }
		  else if (option.equals("C")){
			Scanner kbd = new Scanner(System.in);
			System.out.print("ENTER DEPARTURE CITY: ");
			from = kbd.nextLine();
			System.out.print("ENTER ARRIVAL CITY: ");
			to = kbd.nextLine();
			System.out.print("ENTER HIGH PRICE: ");
			high = kbd.nextLine();
			System.out.print("ENTER LOW PRICE: ");
			low = kbd.nextLine();
			query = "UPDATE PRICE SET HIGH_PRICE ="+high+", LOW_PRICE="+low+" WHERE (DEPARTURE_CITY='"+from+"') AND ARRIVAL_CITY='"+to+"'" ;
			statement.executeUpdate(query);
		  	//System.out.println("DEBUG");
		  	connection.commit();
		  } 

	}	
	catch(Exception Ex)  {
		System.out.println("Machine Error: " +
				   Ex.toString());
	    }
	
    }//end of task 3
    ///////////////////TASK 4////////////////////////
    public void Task4() {
	try {
		statement = connection.createStatement();
	    	String type, man, cap, serv, year;

		//load pricing information from file
		Scanner kbd = new Scanner(System.in);
		System.out.print("ENTER INPUT FILE NAME AND PATH: ");
		String fname = kbd.nextLine();
		BufferedReader infile = new BufferedReader(new FileReader(fname));
		while(infile.ready()){
			String text = infile.readLine();
			String[] parse = text.split(" ");
			type = parse[0];
			man = parse[1];
			cap = parse[2];
			serv = parse[3];
			year = parse[4];
			String[] date = serv.split("/");
			System.out.println("PLANE_TYPE: "+type+", MANUFACTURE: "+man+", CAPACITY: "+cap+", LAST_SERVICE: "+serv+", YEAR: "+year);
			query = "insert into PLANE values('"+type+"', '"+man+"', "+cap+", to_date('"+date[0]+"-"+date[1]+"-"+date[2]+"', 'DD-MON-YYYY'), "+year+")" ;
			
			statement.executeUpdate(query);
			//System.out.println("DEBUG");
		  } 
		  connection.commit();
	}	
	catch(Exception Ex)  {
		System.out.println("Machine Error: " +
				   Ex.toString());
	    }
	
    }//end of task 4
    ///////////////////TASK 5////////////////////////
    public void Task5() {
	try {
		statement = connection.createStatement();
	    	String flightNum, date;
		Scanner kbd = new Scanner(System.in);
		System.out.print("ENTER FLIGHT NUMBER: ");
		flightNum = kbd.nextLine();
		System.out.print("DATE DD-MM-YYYY: ");
		date = kbd.nextLine();
		//materialized vew to query from is called mv_manifest
		//select salutation, first_name, last_name from mv_manifest where flight_number = flightNum and flight_date = date
		query = "select distinct salutation, first_name, last_name from mv_manifest where flight_num = '" +flightNum+"' and flight_date = '"+ date+"'";
		resultSet = statement.executeQuery(query);
		int counter = 1;
		while(resultSet.next()){
			System.out.println(counter+" "+resultSet.getString(1)+ " "+resultSet.getString(2) + " "+resultSet.getString(3));
			counter++;
		}
	}	
	catch(Exception Ex)  {
		System.out.println("Machine Error: " +
				   Ex.toString());
	    }
	
    }//end of task 5
 
  public static void main(String args[])
  {
    String menu = "ERASE DATABASE\nLOAD SCHEDULE INFO\nLOAD PRICING INFO\n\t+option \"L\": load from file\n\t+option \"C\": change existing flight\nLOAD PLANE INFO\nGENERATE MANIFEST\nEXIT";


    AdminInterface demo = new AdminInterface();
    Scanner kbd = new Scanner(System.in);
    
    
    
    while(true){
	System.out.println(menu);
	System.out.print("ENTER TASK: ");
	String option = kbd.nextLine();
	if(option.equals("EXIT")){
		break;
	}
	else if(option.equals("ERASE DATABASE")){
		demo.Task1();
	}
	else if(option.equals("LOAD SCHEDULE INFO")){
		demo.Task2();
		
	}//end load schedule info
	else if(option.equals("LOAD PRICING INFO")){
		System.out.print("ENTER OPTION L/C: ");
		String opt = kbd.nextLine();
		if(!(opt.equals("L") || opt.equals("C"))){
			System.out.println("INVALID OPTION\n RETURN TO MENU");
			continue;
		}
		else demo.Task3(opt);
		continue;
	}
	else if(option.equals("LOAD PLANE INFO")){
		demo.Task4();
	}
	else if(option.equals("GENERATE MANIFEST")){
		demo.Task5();
	}
	else{
		System.out.println("MENU OPTION NOT VALID. VALID OPTIONS ARE:");
		System.out.println(menu);
	}
    }//end while loop
    try{demo.connection.close();}catch(Exception Ex){System.out.println("Machine Error: " + Ex.toString());}

  }//EOM
}//EOF
